package cn.bestick.cblog.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import cn.bestick.cblog.dao.CategoryDao;
import cn.bestick.cblog.dmo.Category;
import cn.bestick.cblog.dto.CategoryDto;

public class CategoryDaoImpl extends BaseDao implements CategoryDao {

	Connection con = null;

	public List<CategoryDto> all() throws Exception {
		con = getCon();

		List<CategoryDto> categories = new ArrayList<CategoryDto>();
		String sql = "SELECT t_category.categoryId,categoryName,COUNT(articleId) AS categoryCount FROM t_article RIGHT JOIN t_category ON t_article.categoryId=t_category.categoryId GROUP BY categoryName;";
		PreparedStatement pstmt = con.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()) {
			CategoryDto category = new CategoryDto();
			category.setId(rs.getInt("categoryId"));
			category.setName(rs.getString("categoryName"));
			category.setCount(rs.getInt("categoryCount"));
			categories.add(category);
		}

		close(con);
		return categories;
	}

	public void update(Category category) throws Exception {
		con = getCon();

		String sql = "update t_category set categoryName=? where categoryId=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, category.getName());
		pstmt.setInt(2, category.getId());
		pstmt.executeUpdate();

		close(con);
	}

	public void save(Category category) throws Exception {
		con = getCon();

		String sql = "insert into t_category values(null,?)";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, category.getName());
		pstmt.executeUpdate();

		close(con);
	}

	public void delete(Integer id) throws Exception {
		con = getCon();

		String sql = "delete from t_category where categoryId=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, id);
		pstmt.executeUpdate();

		close(con);
	}

	public Category get(Integer id) throws Exception {
		con = getCon();

		String sql = "SELECT * from t_category where categoryId=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, id);
		ResultSet rs = pstmt.executeQuery();
		Category category = new Category();
		if (rs.next()) {
			category.setId(rs.getInt("categoryId"));
			category.setName(rs.getString("categoryName"));
		}

		close(con);
		return category;
	}

	@Override
	public boolean exist(int categoryId) throws Exception {
		con = getCon();

		String sql = "SELECT * from t_article where categoryId=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, categoryId);
		ResultSet rs = pstmt.executeQuery();
		boolean result = false;
		if (rs.next()) {
			result = true;
		}
		close(con);
		return result;
	}
}
